
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

import analysisplan.analysisplan;
import jxl.JXLException;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Scanner;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DmLog {
	String jdbcString = "dm.jdbc.driver.DmDriver";

	static int get_plan = 0;
	static String conf_path = "";
	
	static String quneName="";
	
	static String urlString = "";
	static String port = "";
	static String userName = "";
	static String password = "";
	static String path = "";
	static Connection conn = null;
	static Connection conn1 = null;
	static int time = 1000;
	static int num = 100;
	static int tmp = 0;
	static int tmp1 = 0;
	static int para = 0;
	static String port1 = "";
	static String urlString1;
	static String userName1;
	static String password1;
	static analysisplan pp = new analysisplan();

	public DmLog() {
		// this.urlString1 = "jdbc:dm://10.12.xx.xx:" + this.port1;
		// this.userName1 = "xx";
		// this.password1 = "xx";
	}

	public static void readTxtFile() {
		try {
			String filePath = conf_path;

			if (filePath.length() > 0) {
			} else {
				filePath = "conf.conf";
			}

			String encoding = "GBK";
			File file = new File(filePath);

			if (file.isFile() && file.exists()) { // 判断文件是否存在

				InputStreamReader read = new InputStreamReader(new FileInputStream(file), encoding);// 考虑到编码格式

				BufferedReader bufferedReader = new BufferedReader(read);
				String lineTxt = null;

				String PP = "";

				while ((lineTxt = bufferedReader.readLine()) != null) {
					String[] test = lineTxt.split("-#-#-#-#");
					ArrayList<File> arry = new ArrayList<File>();
					if (lineTxt.length() > 0 && lineTxt.indexOf("-#-#-#-#") < 0) {
						try {
							PP = lineTxt.split(" ")[0];
							switch (PP) {
							case "urlString":
								urlString = lineTxt.split(" ")[2];
								continue;
							case "quneName":
								quneName = lineTxt.split(" ")[2];
								continue;
							case "userName":
								userName = lineTxt.split(" ")[2];
								continue;
							case "password":
								password = lineTxt.split(" ")[2];
								continue;
							case "urlString1":
								urlString1 = lineTxt.split(" ")[2];
								continue;
							case "userName1":
								userName1 = lineTxt.split(" ")[2];
								continue;
							case "password1":
								password1 = lineTxt.split(" ")[2];
								continue;
							case "path":
								path = lineTxt.split(" ")[2];
								continue;
							case "time":
								time = Integer.parseInt((lineTxt.split(" ")[2]));
								continue;
							case "num":
								num = Integer.parseInt((lineTxt.split(" ")[2]));
								continue;
							case "para":
								para = Integer.parseInt((lineTxt.split(" ")[2]));
								continue;
							case "get_plan":
								get_plan = Integer.parseInt((lineTxt.split(" ")[2]));
								continue;

							}

						} catch (Exception e) {
							System.out.println(lineTxt.split(" ")[0]);

							System.out.println(lineTxt.split(" ")[2]);
							// e.printStackTrace();
						}

					}

				}

				read.close();
			} else {
				System.out.println("读取配置文件失败 ===>>>找不到指定的文件");
				System.exit(0);
			}
		} catch (Exception e) {
			System.out.println("读取文件内容出错");
			e.printStackTrace();
		}

	}

	private static boolean match(String regex, String str) {
		Pattern pattern = Pattern.compile(regex);
		Matcher matcher = pattern.matcher(str);
		return matcher.lookingAt();
	}

	public static boolean isDate(String str) {
		String regex = "^((((1[6-9]|[2-9]\\d)\\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\\d|3[01]))|(((1[6-9]|[2-9]\\d)\\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\\d|30))|(((1[6-9]|[2-9]\\d)\\d{2})-0?2-(0?[1-9]|1\\d|2[0-8]))|(((1[6-9]|[2-9]\\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-)) (20|21|22|23|[0-1]?\\d):[0-5]?\\d:[0-5]?\\d$";
		return match(regex, str);
	}

	public static boolean isNumeric(String str) {
		if (str == null) {
			return false;
		} else {
			int sz = str.length();

			for (int i = 0; i < sz; ++i) {
				if (!Character.isDigit(str.charAt(i)) && str.charAt(i) != '.' && (i != 0 || str.charAt(i) != '-')) {
					return false;
				}
			}

			return true;
		}
	}

	public static int getWordCount(String s) {
		int length = 0;

		for (int i = 0; i < s.length(); ++i) {
			int ascii = Character.codePointAt(s, i);
			if (ascii >= 0 && ascii <= 255) {
				++length;
			} else {
				length += 2;
			}
		}

		return length;
	}

	public static String replaceWenhao(String s, String p) {
		String temp = null;
		if (p == null) {
			return s;
		} else if (p.indexOf("?") > 0) {
			return "/*参数中包含问号*/ " + s;
		} else if (p == "参数过长") {
			return "/*参数过长*/ " + s;
		} else if (p == "找不到有效的参数") {
			return "/*找不到有效的参数*/ " + s;
		} else if (p.indexOf("}") >= 0 && p.indexOf("{") >= 0) {
			while (s.indexOf("?") != -1) {
				if (p.indexOf("), (") <= -1) {
					temp = p.substring(p.lastIndexOf(",") + 1, p.length() - 2);
					s = s.replaceFirst("\\?", Matcher.quoteReplacement(temp.trim()));
					break;
				}

				temp = p.substring(0, p.indexOf("), ("));
				p = p.substring(p.indexOf("), (") + 2, p.length());
				temp = temp.substring(temp.lastIndexOf(",") + 1, temp.length());
				s = s.replaceFirst("\\?", Matcher.quoteReplacement(temp.trim()));
			}

			return "/*参数已替换*/ " + s;
		} else {
			return "/*找不到有效的参数*/ " + s;
		}
	}

	public void loadJdbcDriver() throws SQLException {
		try {
			Class.forName(this.jdbcString);
		} catch (ClassNotFoundException var2) {
			throw new SQLException("Load JDBC Driver Error : " + var2.getMessage());
		} catch (Exception var3) {
			throw new SQLException("Load JDBC Driver Error : " + var3.getMessage());
		}
	}

	public void connect() throws SQLException {
		try {
			conn = DriverManager.getConnection(this.urlString, this.userName, this.password);
		} catch (SQLException var2) {
			throw new SQLException("Connect to DM Server Error : " + var2.getMessage());
		}
	}

	public void connect1() throws SQLException {
		try {
			conn1 = DriverManager.getConnection(this.urlString1, this.userName1, this.password1);
		} catch (SQLException var2) {
			throw new SQLException("Connect to DM Server Error : " + var2.getMessage());
		}
	}

	public void disConnect() throws SQLException {
		try {
			conn.close();
			conn1.close();
		} catch (SQLException var2) {
			throw new SQLException("close connection error : " + var2.getMessage());
		}
	}

	private int readFilePath(String path, String destDirName, int para) throws Exception {
		SinoDetect sinodetector = new SinoDetect();
		File file = new File(path);
		File[] array = file.listFiles();
		FileWriter fw = new FileWriter(destDirName + File.separator + "SQL_ERR_LOG.txt", true);
		FileWriter fw1 = new FileWriter(destDirName + File.separator + "DATA_ERR_LOG.txt", true);
		if (array.length > 0) {
			for (int i = 0; i < array.length; ++i) {
				if (array[i].isFile()) {
					if (array[i].getName().substring(array[i].getName().indexOf(".") + 1, array[i].getName().length())
							.equals("log")) {
						System.out.println("----------------分析文件：" + array[i].getName() + "----------------");
						readFileByLines(array[i].getPath(), destDirName, fw, fw1, sinodetector.detectEncoding(array[i]),
								para);
					} else {
						System.out.println("----------------忽略非SQL日志文件：" + array[i].getName() + "-------------");
					}
				} else if (array[i].isDirectory()) {
					this.readFilePath(array[i].getPath(), destDirName, para);
				}
			}

			fw.close();
			fw1.close();
			return 1;
		} else {
			return 0;
		}
	}

	public static void insert(String fileName, int line, String tempString, ArrayList<String> list, int para1,String destDirName)
			throws SQLException {
		conn.setAutoCommit(false);
		String sqlstr = "insert into log_commit_"+destDirName+"(ID,STARTTIME,SESS,SQLSTR,SQLSTRT,SQLPLAN,SQLPLANT,EXETIME,SQLSTR_SUB,EUER) values("+quneName+".nextval,?,?,?,?,?,?,?,rtrim(?),?)";
		PreparedStatement stmt = conn.prepareStatement(sqlstr);
		String euser = "";
		String para = null;
		String starttime = tempString.substring(0, 19);
		int first = tempString.substring(0, 19).indexOf(45);
		int second = tempString.substring(0, 19).lastIndexOf(45);
		if (first == second) {
			starttime = null;
		}

		String sess;
		if (tempString.indexOf("(") > tempString.indexOf(")")) {
			sess = "";
		} else {
			sess = tempString.substring(tempString.indexOf("("), tempString.indexOf(")") + 1);
			if (sess.indexOf("user:") > 0) {
				euser = sess.substring(sess.indexOf("user:") + 5, sess.indexOf("trxid:"));
			}
		}

		String str1;
		if (tempString.indexOf("(EP[") > 0) {
			str1 = tempString.substring(tempString.indexOf("]") + 2, tempString.length());
			if (tempString.indexOf("MALID[") > 0) {
			str1 = str1.substring(str1.indexOf("]") + 2, str1.length());
			}
			str1 = str1.substring(str1.indexOf("]") + 2, str1.indexOf("EXECTIME:"));
		} else if (tempString.indexOf("]") > tempString.indexOf("EXECTIME:")) {
			System.out.println("###错乱的SQL:" + tempString);
			tempString = tempString.substring(tempString.indexOf("(ms)") + 4, tempString.length());
			str1 = tempString.substring(tempString.indexOf("]") + 2, tempString.indexOf("EXECTIME:"));
			System.out.println("###修正的SQL:" + tempString);
		} else {
			
			str1 = tempString.substring(tempString.indexOf("]") + 2, tempString.indexOf("EXECTIME:"));
			if (tempString.indexOf("MALID[") > 0) {
				str1 = str1.substring(str1.indexOf("]") + 2, str1.length());
				}
		}

		String str2;
		if (str1.length() > 4000) {
			str2 = str1.substring(0, 4000);
			if (getWordCount(str2) > 4000) {
				str2 = str2.substring(0, 3000);
			}
		} else {
			str2 = str1;
		}

		String exetime = tempString.substring(tempString.indexOf("EXECTIME:") + 10, tempString.indexOf("(ms)"));
		if (!isNumeric(exetime)) {
			exetime = "";
		}

		if (str1.indexOf("?") > 0) {
			for (int i = list.size() - 1; i >= 0; --i) {
				if (((String) list.get(i)).length() > 1 && ((String) list.get(i)).indexOf(sess) > 0
						&& ((String) list.get(i)).indexOf("PARAMS(SEQNO, TYPE, DATA)") > 0) {
					para = ((String) list.get(i)).substring(
							((String) list.get(i)).indexOf("PARAMS(SEQNO, TYPE, DATA)") + 26,
							((String) list.get(i)).length());
					list.remove(i);
					break;
				}
			}

			if (para == null) {
				para = "找不到有效的参数";
			}
		}

		if (para != null && para.length() > 4000) {
			para = "参数过长";
		}

		stmt.setString(3, str1);
		if (get_plan == 0) {
			stmt.setString(5, null);
		} else {
			stmt.setString(5, analysisplan.getExecutePlan(str1, conn1));
		}
		if (para1 == 1) {
			str1 = replaceWenhao(str1, para);
			str2 = replaceWenhao(str2, para);
		}

		stmt.setString(1, starttime);
		stmt.setString(2, sess);
		stmt.setString(4, str1);

		if (get_plan == 0) {
			stmt.setString(6, null);
		} else {
			stmt.setString(6, analysisplan.getExecutePlan(str1, conn1));
		}
		stmt.setString(7, exetime);
		stmt.setString(8, str2);
		stmt.setString(9, euser);
		if (starttime != null && sess != null && (starttime.length() > 30 || sess.length() > 500)) {
			System.out.println(starttime);
			System.out.println(sess);
		}

		stmt.execute();
		stmt.close();
		if (line % 5000 == 0) {
			conn.commit();
		}

	}

	public static void readFileByLines(String fileName, String destDirName, FileWriter fw, FileWriter fw1, int Encode,
			int para) throws SQLException {
		File file = new File(fileName);
		ArrayList<String> list = new ArrayList();
		BufferedReader reader = null;
		String tempString = null;
		String tempString1 = null;
		String tempString2 = null;

		try {
			String coding;
			if (Encode == 6) {
				coding = "UTF-8";
			} else {
				coding = "GBK";
			}

			InputStreamReader read = new InputStreamReader(new FileInputStream(file), coding);
			reader = new BufferedReader(read);
			int line = 1;
			int cnt = 0;

			while ((tempString = reader.readLine()) != null) {
				try {
				if (tempString.indexOf("PARAMS(SEQNO, TYPE, DATA)") > 0) {
					list.add(tempString);
					if (list.size() == 3000) {
						list.remove(0);
					}
				}

				if (tempString.indexOf("sess:") > 0) {
					++cnt;
				}

				if (tempString.indexOf("sess:") < 0 && tempString.length() > 0 && cnt == 0 && line >= 10) {
					System.out.println("无效的sql日志文件：" + fileName);
					break;
				}

				if (tempString.length() > 1 && tempString.indexOf("[ERR(") > 0
						&& (tempString.indexOf("sess:") > 0 || tempString.indexOf("(EP[") > 0)) {
					fw.write(tempString, 0, tempString.length());
					fw.write("\r\n\r\n");
					fw.flush();
				}

				if (tempString.length() > 1 && tempString.indexOf(") [") > 0 && tempString.indexOf("[ORA]") < 0
						&& tempString.indexOf("[ERR") < 0) {
					if (tempString.indexOf("(sess") >= 0) {
						System.out.println("temp 001");
						tempString.indexOf("(EP[");
					} else {
						for (; tempString.indexOf("EXECTIME:") < 0 && tempString.indexOf("(ms)") < 0; ++line) {
							tempString1 = reader.readLine();
							if (tempString1.indexOf("PARAMS(SEQNO, TYPE, DATA)") > 0) {
								list.add(tempString1);
								if (list.size() == 3000) {
									list.remove(0);
								}

								tempString2 = "###无效的SQL " + line + "行附近：" + tempString;
								fw1.write(tempString2, 0, tempString2.length());
								fw1.write("\r\n\r\n");
								fw1.flush();
								tempString = null;
								break;
							}

							if (tempString1.length() > 19 && isDate(tempString1.substring(0, 19))
									&& tempString.indexOf("EXECTIME:") < 0 && tempString.indexOf("(ms)") < 0) {
								tempString2 = "###无效的SQL " + line + "行附近：" + tempString;
								fw1.write(tempString2, 0, tempString2.length());
								fw1.write("\r\n\r\n");
								fw1.flush();
								tempString = null;
								break;
							}

							if (tempString1 == null) {
								tempString2 = "###无效的SQL " + line + "行附近：" + tempString1;
								fw1.write(tempString2, 0, tempString2.length());
								fw1.write("\r\n\r\n");
								fw1.flush();
								tempString = null;
								break;
							}

							tempString = tempString + "\r\n" + tempString1;
							if (tempString.lastIndexOf("ip:::ffff:") > tempString.indexOf("ip:::ffff:")) {
								tempString2 = "###无效的SQL " + line + "行附近：" + tempString.substring(0,
										tempString.lastIndexOf("ip:::ffff:") - tempString.indexOf("ip:::ffff:"));
								if (tempString2.indexOf("[ORA]") < 0 && tempString2.indexOf(") [") > 0
										&& tempString2.indexOf("[ERR") < 0) {
									fw1.write(tempString2, 0, tempString2.length());
									fw1.write("\r\n\r\n");
									fw1.flush();
								}

								tempString = tempString.substring(
										tempString.lastIndexOf("ip:::ffff:") - tempString.indexOf("ip:::ffff:"),
										tempString.length());
							}
						}

						if (tempString != null) {
							insert(fileName, line, tempString, list, para,destDirName);
						}
					}
				}

				++line;
				}catch (Exception e) {
							;
				}
				if (line % 500000 == 0) {
					System.out.println(fileName + "文件已处理：" + line + "行");
				}
			}

			conn.commit();
			reader.close();
		} catch (IOException var26) {
			var26.printStackTrace();
			if (reader != null) {
				try {
					reader.close();
				} catch (IOException var25) {
					;
				}
			}
		} finally {
			if (reader != null) {
				try {
					reader.close();
				} catch (IOException var24) {
					;
				}
			}

		}

	}

	private static void displayResultSet(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int numCols = rsmd.getColumnCount();

		int i;
		for (i = 1; i <= numCols; ++i) {
			if (i > 1) {
				System.out.print(",");
			}

			System.out.print(rsmd.getColumnLabel(i));
		}

		System.out.println("");

		while (rs.next()) {
			for (i = 1; i <= numCols; ++i) {
				if (i > 1) {
					System.out.print(",");
				}

				System.out.print(rs.getString(i));
			}

			System.out.println("");
		}

	}

	public void createTable(String destDirName) throws SQLException {
		int count = 0;
		String sql1 = "select count(*) from user_tables where table_name='log_commit_"+destDirName+"'";
		String sql2 = "create table log_commit_"+destDirName+"(id bigint,starttime varchar(30),sess varchar(500),sqlstr text,sqlstrT text,sqlplan text,sqlplanT text,exetime float,sqlstr_sub varchar(8000),euer varchar(100));";
		String sql3 = "DROP TABLE log_commit_"+destDirName+"";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql1);
		rs.next();
		count = rs.getInt(1);
		if (count == 0) {
			stmt.execute(sql2);
		} else {
			stmt.execute(sql3);
			stmt.execute(sql2);
		}

		rs.close();
		stmt.close();
	}

	public static void writeExcelTime(String fileName, int num2, String destDirName)
			throws SQLException, JXLException, IOException {
		WritableWorkbook wwb = null;

		try {
			wwb = Workbook.createWorkbook(new File(destDirName + File.separator + fileName));
		} catch (IOException var56) {
			var56.printStackTrace();
		}

		int charTitle = 12;
		int charNormal = 10;
		WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), charTitle, WritableFont.BOLD, false,
				UnderlineStyle.NO_UNDERLINE, Colour.WHITE);
		WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
		titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		titleFormat.setAlignment(Alignment.CENTRE);
		titleFormat.setWrap(true);
		titleFormat.setBackground(jxl.write.Colour.BLUE_GREY);
		WritableFont normalFont = new WritableFont(WritableFont.createFont("宋体"), charNormal);
		WritableCellFormat normalFormat = new WritableCellFormat(normalFont);
		normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		normalFormat.setAlignment(Alignment.CENTRE);
		normalFormat.setWrap(false);
		WritableCellFormat normalFormat1 = new WritableCellFormat(normalFont);
		normalFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);
		normalFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
		normalFormat1.setAlignment(Alignment.LEFT);
		normalFormat1.setWrap(false);
		WritableCellFormat normalFormat2 = new WritableCellFormat(normalFont);
		normalFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
		normalFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
		normalFormat2.setAlignment(Alignment.LEFT);
		normalFormat2.setWrap(false);
		normalFormat2.setBackground(jxl.write.Colour.VERY_LIGHT_YELLOW);
		int count = 0;
		String sql1 = "select count(distinct sqlstr_sub) from log_commit_"+destDirName+" where lower(sess) not like '%appname:dmhs%' and exetime >= 1000";
		String sql2 = "select s , max_tim , min_tim , CEILING(avg_90) avg90 , CEILING(avg_tim) avg100, cnt from (  select s , max(tim)over(partition by sub) max_tim , min(tim)over(partition by sub) min_tim , avg(tim)over(partition by sub) avg_tim , avg(case when tile<=9 then tim else null end)over(partition by sub) avg_90, count(1)over(partition by sub) cnt , row_number()over(partition by sub order by tim desc) r  from ( select sess, ifnull(sqlstr, cast(sqlstr_sub as clob)) s, sqlstr_sub sub  , exetime tim  , ntile(10) over(partition by sqlstr_sub order by exetime) tile from log_commit_"+destDirName+" where exetime >= 1000 and lower(sess) not like '%appname:dmhs%' ) t1 ) where r = 1 order by max_tim desc ";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql1);
		rs.next();
		count = rs.getInt(1);
		rs.close();
		stmt.close();
		Statement stmt2 = conn.createStatement();
		ResultSet rs2 = stmt2.executeQuery(sql2);
		if (wwb != null) {
			WritableSheet ws = wwb.createSheet("SQL分析", 0);
			ws.setColumnView(0, 10);
			ws.setColumnView(1, 50);
			ws.setColumnView(2, 18);
			ws.setColumnView(3, 18);
			ws.setColumnView(4, 20);
			ws.setColumnView(5, 18);
			ws.setColumnView(6, 15);
			ws.setColumnView(7, 20);
			ws.setColumnView(8, 15);
			Label tit0 = new Label(0, 0, "序号", titleFormat);
			Label tit1 = new Label(1, 0, "SQL语句", titleFormat);
			Label tit2 = new Label(2, 0, "最大执行时间", titleFormat);
			Label tit3 = new Label(3, 0, "最小执行时间", titleFormat);
			Label tit4 = new Label(4, 0, "90%平均执行时间", titleFormat);
			Label tit5 = new Label(5, 0, "平均执行时间", titleFormat);
			Label tit6 = new Label(6, 0, "执行次数", titleFormat);
			Label tit7 = new Label(7, 0, "优化方法", titleFormat);
			Label tit8 = new Label(8, 0, "优化后时间", titleFormat);

			try {
				ws.addCell(tit0);
				ws.addCell(tit1);
				ws.addCell(tit2);
				ws.addCell(tit3);
				ws.addCell(tit4);
				ws.addCell(tit5);
				ws.addCell(tit6);
				ws.addCell(tit7);
				ws.addCell(tit8);
			} catch (RowsExceededException var54) {
				var54.printStackTrace();
			} catch (WriteException var55) {
				var55.printStackTrace();
			}

			FileInputStream fis = null;
			InputStreamReader isr = null;
			BufferedReader br = null;
			FileOutputStream fos = null;
			PrintWriter pw = null;

			for (int i = 1; i <= count; ++i) {
				rs2.next();

				for (int j = 0; j <= 6; ++j) {
					Label labelC = null;
					if (j == 0) {
						labelC = new Label(j, i, String.valueOf(i), normalFormat);
					} else if (j != 1) {
						labelC = new Label(j, i, rs2.getString(j), normalFormat);
					} else if (rs2.getString(j).length() > 30000) {
						SQLForm sqlForm = new SQLForm();
						sqlForm.setCase(false, false);
						sqlForm.setLowerCase(false);
						sqlForm.setGraphLevel(false);
						sqlForm.setSuppressSpace(true);
						sqlForm.setQuoteCharacter("'");
						sqlForm.setSuppressEmptyLine(true);
						sqlForm.setFormatLanguage("SQL");
						sqlForm.setBracketSpaces("noSpacesAroundBracker");
						sqlForm.setCommaSpaces("oneSpaceAfterComma");
						sqlForm.setAlignmentEqual(!rs2.getString(j).contains("!="));

						try {
							File filename = new File(destDirName + File.separator + "Q" + i + ".txt");
							if (!filename.exists()) {
								filename.createNewFile();
							}

							fis = new FileInputStream(filename);
							isr = new InputStreamReader(fis, "GBK");
							br = new BufferedReader(isr);
							StringBuffer buf = new StringBuffer();
							buf = buf.append(sqlForm.formatSQLAsString(rs2.getString(j)));
							fos = new FileOutputStream(filename);
							pw = new PrintWriter(fos);
							pw.write(buf.toString().toCharArray());
							pw.flush();
						} catch (IOException var57) {
							throw var57;
						} finally {
							if (pw != null) {
								pw.close();
							}

							if (fos != null) {
								fos.close();
							}

							if (br != null) {
								br.close();
							}

							if (isr != null) {
								isr.close();
							}

							if (fis != null) {
								fis.close();
							}

						}

						labelC = new Label(j, i, rs2.getString(j).substring(0, 30000), normalFormat2);
					} else {
						labelC = new Label(j, i, rs2.getString(j), normalFormat1);
					}

					try {
						ws.addCell(labelC);
					} catch (RowsExceededException var52) {
						var52.printStackTrace();
					} catch (WriteException var53) {
						var53.printStackTrace();
					}
				}
			}

			rs2.close();
			stmt2.close();

			try {
				wwb.write();
				wwb.close();
			} catch (IOException var50) {
				var50.printStackTrace();
			} catch (WriteException var51) {
				var51.printStackTrace();
			}
		}

	}

	public static void writeExcelNum(String fileName, int num2, String destDirName)
			throws SQLException, JXLException, IOException {
		WritableWorkbook wwb = null;

		try {
			wwb = Workbook.createWorkbook(new File(destDirName + File.separator + fileName));
		} catch (IOException var56) {
			var56.printStackTrace();
		}

		int charTitle = 12;
		int charNormal = 10;
		WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), charTitle, WritableFont.BOLD, false,
				UnderlineStyle.NO_UNDERLINE, Colour.WHITE);
		WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
		titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		titleFormat.setAlignment(Alignment.CENTRE);
		titleFormat.setWrap(true);
		titleFormat.setBackground(jxl.write.Colour.BLUE_GREY);
		WritableFont normalFont = new WritableFont(WritableFont.createFont("宋体"), charNormal);
		WritableCellFormat normalFormat = new WritableCellFormat(normalFont);
		normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		normalFormat.setAlignment(Alignment.CENTRE);
		normalFormat.setWrap(false);
		WritableCellFormat normalFormat1 = new WritableCellFormat(normalFont);
		normalFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);
		normalFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
		normalFormat1.setAlignment(Alignment.LEFT);
		normalFormat1.setWrap(false);
		WritableCellFormat normalFormat2 = new WritableCellFormat(normalFont);
		normalFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
		normalFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
		normalFormat2.setAlignment(Alignment.LEFT);
		normalFormat2.setWrap(false);
		normalFormat2.setBackground(jxl.write.Colour.VERY_LIGHT_YELLOW);
		int count = 0;
		String sql1 = " select  count(*) from (    select 1 from log_commit_"+destDirName+" group by sqlstr_sub having count(*) >= ?);";
		String sql2 = " select s,max_tim,min_tim,CEILING(avg_90) avg90,CEILING(avg_tim) avg100,cnt from (  select          s ,          max(tim)over(partition by sub) max_tim,          min(tim)over(partition by sub) min_tim,          avg(tim)over(partition by sub) avg_tim,          avg(case when tile<=9 then tim else null end)over(partition by sub) avg_90,          count(1)over(partition by sub) cnt,          row_number()over(partition by sub order by tim desc) r   from           (                  select                             ifnull(sqlstr,cast(sqlstr_sub as clob)) s     ,                          sqlstr_sub sub,                          exetime tim,                          ntile(10) over(partition by sqlstr_sub order by exetime) tile                 from                          log_commit_"+destDirName+"          ) t1         )  where r = 1 and cnt>=? order by  cnt desc";
		PreparedStatement stmt = conn.prepareStatement(sql1);
		stmt.setInt(1, num2);
		ResultSet rs = stmt.executeQuery();
		rs.next();
		count = rs.getInt(1);
		rs.close();
		stmt.close();
		PreparedStatement stmt2 = conn.prepareStatement(sql2);
		stmt2.setInt(1, num2);
		ResultSet rs2 = stmt2.executeQuery();
		if (wwb != null) {
			WritableSheet ws = wwb.createSheet("SQL分析", 0);
			ws.setColumnView(0, 10);
			ws.setColumnView(1, 50);
			ws.setColumnView(2, 18);
			ws.setColumnView(3, 18);
			ws.setColumnView(4, 20);
			ws.setColumnView(5, 18);
			ws.setColumnView(6, 15);
			ws.setColumnView(7, 20);
			ws.setColumnView(8, 15);
			Label tit0 = new Label(0, 0, "序号", titleFormat);
			Label tit1 = new Label(1, 0, "SQL语句", titleFormat);
			Label tit2 = new Label(2, 0, "最大执行时间", titleFormat);
			Label tit3 = new Label(3, 0, "最小执行时间", titleFormat);
			Label tit5 = new Label(4, 0, "90%平均执行时间", titleFormat);
			Label tit4 = new Label(5, 0, "平均执行时间", titleFormat);
			Label tit6 = new Label(6, 0, "执行次数", titleFormat);
			Label tit7 = new Label(7, 0, "优化方法", titleFormat);
			Label tit8 = new Label(8, 0, "优化后时间", titleFormat);

			try {
				ws.addCell(tit0);
				ws.addCell(tit1);
				ws.addCell(tit2);
				ws.addCell(tit3);
				ws.addCell(tit4);
				ws.addCell(tit5);
				ws.addCell(tit6);
				ws.addCell(tit7);
				ws.addCell(tit8);
			} catch (RowsExceededException var54) {
				var54.printStackTrace();
			} catch (WriteException var55) {
				var55.printStackTrace();
			}

			FileInputStream fis = null;
			InputStreamReader isr = null;
			BufferedReader br = null;
			FileOutputStream fos = null;
			PrintWriter pw = null;

			for (int i = 1; i <= count; ++i) {
				rs2.next();

				for (int j = 0; j <= 6; ++j) {
					Label labelC = null;
					if (j == 0) {
						labelC = new Label(j, i, String.valueOf(i), normalFormat);
					} else if (j != 1) {
						labelC = new Label(j, i, rs2.getString(j), normalFormat);
					} else if (rs2.getString(j).length() > 10000) {
						SQLForm sqlForm = new SQLForm();
						sqlForm.setCase(false, false);
						sqlForm.setLowerCase(false);
						sqlForm.setGraphLevel(false);
						sqlForm.setSuppressSpace(true);
						sqlForm.setQuoteCharacter("'");
						sqlForm.setSuppressEmptyLine(true);
						sqlForm.setFormatLanguage("SQL");
						sqlForm.setBracketSpaces("noSpacesAroundBracker");
						sqlForm.setCommaSpaces("oneSpaceAfterComma");
						sqlForm.setAlignmentEqual(!rs2.getString(j).contains("!="));

						try {
							File filename = new File(destDirName + File.separator + "Q" + i + ".txt");
							if (!filename.exists()) {
								filename.createNewFile();
							}

							fis = new FileInputStream(filename);
							isr = new InputStreamReader(fis, "GBK");
							br = new BufferedReader(isr);
							StringBuffer buf = new StringBuffer();
							buf = buf.append(sqlForm.formatSQLAsString(rs2.getString(j)));
							fos = new FileOutputStream(filename);
							pw = new PrintWriter(fos);
							pw.write(buf.toString().toCharArray());
							pw.flush();
						} catch (IOException var57) {
							throw var57;
						} finally {
							if (pw != null) {
								pw.close();
							}

							if (fos != null) {
								fos.close();
							}

							if (br != null) {
								br.close();
							}

							if (isr != null) {
								isr.close();
							}

							if (fis != null) {
								fis.close();
							}

						}

						labelC = new Label(j, i, rs2.getString(j).substring(0, 10000), normalFormat2);
					} else {
						labelC = new Label(j, i, rs2.getString(j), normalFormat1);
					}

					try {
						ws.addCell(labelC);
					} catch (RowsExceededException var52) {
						var52.printStackTrace();
					} catch (WriteException var53) {
						var53.printStackTrace();
					}
				}
			}

			rs2.close();
			stmt2.close();

			try {
				wwb.write();
				wwb.close();
			} catch (IOException var50) {
				var50.printStackTrace();
			} catch (WriteException var51) {
				var51.printStackTrace();
			}
		}

	}

	public static void main(String[] args) throws Exception {
		Calendar cal = Calendar.getInstance();
		int hour = cal.get(11);
		int minute = cal.get(12);
		int ss = cal.get(13);
		Scanner s = new Scanner(System.in);
		DmLog dmLog = new DmLog();
		
		if(args.length>0) {
		System.out.println(args[0]);
		conf_path = args[0];
		}

		System.out.println("##############");
		readTxtFile();

		System.out.println("############################# dm7日志分析程序使用说明 ########################");
		System.out.println("###                                                                        ###");
		System.out.println("### 1.请确认sql trace参数，确保每条语句后紧跟sql语句时间: 1:25             ###");
		System.out.println("### 2.本程序建表log_commit进行分析                                         ###");
		System.out.println("### 3.本程序建表前会删除同名表，请做好备份 ！                              ###");
		System.out.println("### 4.请使用页大小为32k的DM7进行分析 ！                                    ###");
		System.out.println("### 5.结果中sql语句背景为黄色的表示sql长度超过30000,已截断 ！              ###");
		System.out.println("### 6.截断的语句会保存到文本文件中，如第一条截断会生成Q1.txt ！            ###");
		System.out.println("### 7.本程序生成的所有文件存放在当前目录下的RESULT_$DATE目录下 ！          ###");
		System.out.println("### 8.本程序只能连测试环境进行分析，切忌连生产环境！！！                   ###");
		System.out.println("###                                                                        ###");
		System.out.println("############################# 说明完毕，请使用！##############################");
		System.out.println("");
		// System.out.println("使用本机默认DM7数据源请输入0，指定数据源请输入1：");

		System.out.println("分析存储库===>>>" + urlString);
		System.out.println("用户名===>>>" + userName);
		System.out.println("");
		System.out.println("计划获取库===>>>" + urlString1);
		System.out.println("用户===>>>" + userName1);
		System.out.println("");
		System.out.println("其他指标类参数");
		System.out.println("慢日志路径===>>>" + path);
		System.out.println("语句执行时间下限 ms===>>>" + time);
		System.out.println("语句执行次数下限===>>>" + num);
		System.out.println("是否替换参数===>>>" + para);
		System.out.println("");

		tmp1 = 0;
		Scanner sc;
		if (tmp1 == 1) {
			sc = new Scanner(System.in);
			System.out.println("请输入IP地址：");
			String ip = sc.nextLine();
			if (ip.length() > 0) {
				dmLog.urlString = "jdbc:dm://" + ip;
			} else {
				dmLog.urlString = "jdbc:dm://localhost";
			}

			System.out.println("请输入端口号：");
			String port = sc.nextLine();
			if (port.length() > 0) {
				dmLog.urlString = dmLog.urlString + ":" + port;
			} else {
				dmLog.urlString = dmLog.urlString + ":" + "5236";
			}

			System.out.println("请输入用户名：");
			String username = sc.nextLine();
			if (username.length() > 0) {
				dmLog.userName = username;
			}

			System.out.println("请输入密码：");
			String pwd = sc.nextLine();
			if (pwd.length() > 0) {
				dmLog.password = pwd;
			}
		}

		// System.out.println("根据日志入库生成分析结果请输入0，根据表中已有数据直接生成分析结果请输入1：");
		tmp = 0;
		dmLog.loadJdbcDriver();
		dmLog.connect();
		dmLog.connect1();
		sc = null;
		SimpleDateFormat df = new SimpleDateFormat("yyyy_MM_dd");
		String destDirName = df.format(new Date());
		destDirName = "RESULT_HK_" + destDirName + "_" + hour + "_" + minute + "_" + ss;
		File dir = new File(destDirName);
		int ret = 0;
		if (tmp == 0) {
			new Scanner(System.in);
			// System.out.println("请输入存放SQL日志的文件夹绝对路径,并保证文件夹中只有SQL日志文件：");
			// String path = path;
			// System.out.println("您想分析多少毫秒以上的SQL语句：");
			// time = time;
			// System.out.println("您想分析执行多少次以上的SQL语句：");
			// num = this.num;
			// System.out.println("您是否需要替换SQL语句中的参数：");
			// para = this.para;
			dmLog.createTable(destDirName);
			if (dir.exists()) {
				System.out.println("创建目录" + destDirName + "失败，目标目录已经存在");
			}

			/*
			 * if (!destDirName.endsWith(File.separator)) { destDirName = destDirName +
			 * File.separator; }
			 */

			if (dir.mkdirs()) {
				System.out.println("创建目录" + destDirName.substring(0, destDirName.length() - 1) + "成功！");
			} else {
				System.out.println("创建目录" + destDirName.substring(0, destDirName.length() - 1) + "失败！");
			}

			ret = dmLog.readFilePath(path, destDirName, para);
			Statement stm1 = conn.createStatement();
			System.out.println("----------------创建索引---------------------");
			stm1.execute("create or replace index idx_exetime_" + destDirName.substring(0, destDirName.length() - 1)
					+ " on log_commit_"+destDirName+"(exetime);");
			stm1.execute("SP_COL_STAT_INIT_EX(USER,'LOG_COMMIT_"+destDirName+"','EXETIME',100);");
			System.out.println("----------------创建索引成功-------------------");
			stm1.close();
			if (ret == 1) {
				System.out.println("----------------入库完毕，开始分析---------------");
				writeExcelTime("more_than_" + time + "_ms_log_result.xls", time, destDirName);
			}
		}

		if (tmp == 1) {
			System.out.println("您想分析多少毫秒以上的SQL语句：");
			time = s.nextInt();
			System.out.println("您想分析执行多少次以上的SQL语句：");
			num = s.nextInt();
			System.out.println("----------------开始分析----------------");
			if (dir.exists()) {
				System.out.println("创建分析结果目录" + destDirName + "失败，目标目录已经存在");
			}

			if (!destDirName.endsWith(File.separator)) {
				destDirName = destDirName + File.separator;
			}

			if (dir.mkdirs()) {
				System.out.println("创建分析结果目录" + destDirName.substring(0, destDirName.length() - 1) + "成功！");
			} else {
				System.out.println("创建分析结果目录" + destDirName.substring(0, destDirName.length() - 1) + "失败！");
			}

			writeExcelTime("more_than_" + time + "_ms_log_result.xls", time, destDirName);
		}

		dmLog.disConnect();
		System.out.println("----------------分析完毕，请查看结果-------------");
	}
}
